package com.fs.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.fs.entity.*;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author author
 * @since 2024-06-12
 */
@Mapper
public interface StudentsMapper extends BaseMapper<Students> {
    @Select("SELECT m.* FROM materials m INNER JOIN students s ON m.sid = s.sid WHERE s.sid = #{sid} AND m.state = 1")
    List<Materials> getMaterialsUnderReviewBySid(@Param("sid")Integer sid);


    @Select("SELECT\n" +
            "    MAX(mc.mcid) AS mcid,\n" +
            "    mc.mcaname,\n" +
            "    COUNT( m.mid ) AS occurrence_count,\n" +
            "    MAX( mc.mckind ) AS mckind,\n" +
            "    MAX( mc.mlevel ) AS mlevel,\n" +
            "    MAX( mc.mcredit ) AS mcredit\n" +
            "  FROM\n" +
            "    materialcategory mc\n" +
            "    INNER JOIN materials m ON mc.mcid = m.mcid \n" +
            "  WHERE\n" +
            "    NOT EXISTS (\n" +
            "      SELECT 1\n" +
            "      FROM materials m2\n" +
            "      WHERE m2.mcid = mc.mcid AND m2.sid = #{sid}\n" +
            "    )\n" +
            "  GROUP BY\n" +
            "    mc.mcaname\n" +
            "  ORDER BY\n" +
            "    occurrence_count DESC")
    List<Materialcategory> selectMaterialCategoriesWithoutStudentSubmissions(@Param("sid")Integer sid);
    Page<StuShow> selectStuPageWithConditions
            (Page<StuShow> page, @Param("condition") StuCondition stuCondition);
//分页查询我的材料
    Page<StuScShow> selectMaterialsByConditions
            (Page<StuScShow> page,@Param("sc") StudentCondition condition,@Param("sid") Integer sid);

}
